use master
go 

create database bbs
on
(
	name='bbs',
	filename='D:\bbs.mdf'
)
log on
(
	name='bbs_log',
	filename='D:\bbs_log.ldf'
)


use bbs
go

create table bbsUsers
(
	UID int identity(1,1),
	uName varchar(10) not null,
	uSex varchar(2) not null,
	uAge int not null,
	uPoint int not null
)


create table bbsSection
(
	sID int identity(1,1) ,
	sName varchar(10) not null ,
	sUid int 
)

create table bbsTopic
(
	tId int identity(1,1),
	tUID int,
	tSID int,
	tTitle varchar(100) not null,
	tMsg text not null,
	tTime datetime,
	tCount int
)


create table bbsReply
(
	rId int identity(1,1),
	rUId int,
	rTID int,
	rMsg text not null,
	rTime datetime
)


alter table bbsUsers add constraint PK_bbsUsers_UID primary key (UID)
alter table bbsUsers add constraint UQ_bbsUsers_uName unique (uName)
alter table bbsUsers add constraint CK_bbsUsers_uSex check(uSex='男' or uSex='女')
alter table bbsUsers add constraint CK_bbsUsers_uAge check(uAge>=15 and uAge<=60)
alter table bbsUsers add constraint CK_bbsUsers_uPoint check(uPoint>=0)

alter table bbsSection add constraint PK_bbsSection_sID primary key (sID)
alter table bbsSection add constraint FK_bbsSection_sUid foreign key(sUID) references bbsUsers(UID) 

alter table bbsTopic add constraint PK_bbsTopic_tID primary key (tID)
alter table bbsTopic add constraint FK_bbsTopic_tUID foreign key(tUID) references bbsUsers(UID)
alter table bbsTopic add constraint FK_bbsTopic_tSID foreign key(tSID) references bbsSection(sID)

alter table bbsReply add constraint PK_bbsReply_rID primary key (rID)
alter table bbsReply add constraint FK_bbsReply_rUID foreign key(rUID) references bbsUsers(UID)
alter table bbsReply add constraint Fk_bbsReply_rTID foreign key(rTID) references bbsTopic(tID)

--	1.现在有3个会员注册成功，请用一次插入多行数据的方法向bbsUsers表种插入3行记录，记录值如下：
--	  小雨点  女  20  0
--	  逍遥    男  18  4
--	  七年级生  男  19  2

insert into bbsUsers(uName,uSex,uAge,uPoint)
select'小雨点','女','20','0'union
select'逍遥','男','18','4'union
select'七年级生','男','19','2'

--	2.将bbsUsers表中的用户名和积分两列备份到新表bbsPoint表中，提示查询部分列:select 列名1，列名2 from 表名

select uName,uPoint into bbsPoint from bbsUsers



--	3.给论坛开设4个板块
--	  名称        版主名
--	  技术交流    小雨点
--	  读书世界    七年级生
--	  生活百科     小雨点
--	  八卦区       七年级生

insert into bbsSection(sName,sUid)
select'技术交流','3'union
select'读书世界','1'union
select'生活百科','3'union
select'八卦区','1'


--	4.向主贴和回帖表中添加几条记录
	  
--	   主贴：

--	  发帖人    板块名    帖子标题                帖子内容                发帖时间   回复数量
--	  逍遥      八卦区     范跑跑                 谁是范跑跑              2008-7-8   1
--	  七年级生  技术交流   .NET                   与JAVA的区别是什么呀？  2008-9-1   2
--	  小雨点   生活百科    今年夏天最流行什么     有谁知道今年夏天最流行  2008-9-10  0
--											      什么呀？
	   
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
select '2','1','范跑跑','谁是范跑跑','2008-7-8','1'union
select '1','3','.NET','与Java的区别是什么呀？','2008-9-1','2'union
select '3','4','今年夏天最流行什么呀？','有谁知道今年夏天最流行什么呀？','2008-9-10','0'


--	   回帖：
--	   分别给上面三个主贴添加对应的回帖，回帖的内容，时间，回帖人自定

insert into bbsReply(rTID,rMsg,rTime,rUId)
select'1','不知道','2008-9-5','2'union
select'2','不认识','2008-9-10','1'union
select'3','不知道','2008-10-1','3'

--	5.因为会员“逍遥”发表了非法帖子，现将其从论坛中除掉，即删除该用户，请用语句实现（注意主外键，要删除主键，先要将引用了该主键的外键数据行删除）



alter table bbsReply drop constraint FK_bbsReply_rUID
alter table bbsTopic drop constraint FK_bbsTopic_tUID
alter table bbsSection drop constraint FK_bbsSection_sUid
alter table bbsUsers drop constraint PK_bbsUsers_UID

delete from bbsUsers where UID='2' 

--	6.因为小雨点发帖较多，将其积分增加10分


update bbsPoint set uPoint=(uPoint + 10) where uName='小雨点'


--	7.因为板块“生活百科”灌水的人太少，现决定取消该板块，即删除（注意主外键）

delete from bbsReply where rTID=3
delete from bbsTopic where tSID=4
delete from bbsSection where sID=4

--	8.因回帖积累太多，现需要将所有的回帖删除

delete from bbsReply where rTID=1 or rTID=2

--1.在主贴表中统计每个版块的发帖总数
select sName as 板块名 , SUM(sUid) 发帖总数 from bbsSection group by sName 

--2.在回帖表中统计每个主贴的回帖总数量
select tTitle as 主贴名 , SUM(tCount) 回帖总数 from bbsTopic group by tTitle

--3.在主贴表中统计每个用户的发的主帖的总数
select tUID , COUNT(tId) as 发帖总数 from bbsTopic group by tUID

--4.在主贴表中统计每个用户发的主贴的回复数量总和
select tUID , SUM(tCount) 回帖总数 from bbsTopic group by tUID

--5.在主贴表中查询每个版块的主贴的平均回复数量大于3的版块的平均回复数量
select tUID , SUM(tCount) 回帖总数 from bbsTopic group by tUID having SUM(tCount)>3

--6.在用户信息表中查询出积分最高的用户的用户名，性别，年龄和积分
select * from bbsUsers where uPoint=(select MAX(uPoint) from bbsUsers)

--7.在主贴表中（bbsTopic）中将帖子的内容或标题中有“快乐”两字的记录查询出来
select * from bbsTopic where tTitle like('%范跑跑%') or tMsg like('%快乐%')

--8.在用户信息表（bbsUsers）中将用户年龄在15-20之间并且积分在10分以上的优秀用户查询出来（用多种方法实现）
select * from bbsUsers where (uAge>15 and uAge<20) and uPoint>10

--9.在用户信息表（bbsUsers）中将用户名的第一个字为“小”，第三字为“大”的用户信息查询出来
select * from bbsUsers where uName like ('小%') and uName like ('__大')

--10.在主贴表（bbsTopic）中将在2008-9-10 12:00:00 以后发的并且回复数量在10以上的帖子的标题和内容查询出来，并且为列取上对应的中文列名
select * from bbsTopic where tTime >= '2008-9-10 12:00:00' and tCount > 10

--11.在主贴表（bbsTopic）中将帖子的标题是以‘！’结尾的帖子的发帖人编号和回复数量查询出来
select tUID , tCount from bbsTopic where tTitle like '%?'